﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;
using StockTrader.FrontEnd.MarketSummary.DAL;

namespace StockTrader.FrontEnd.MarketSummary
{
    public partial class frmMain : Form
    {
        public frmMain()
        {
            InitializeComponent();
        }

        public void ConvertData(DateTime transDate, string sourceFile, string convertedFile)
        {
            try
            {
                int importedRecord = 0;

                if ((File.Exists(sourceFile) && (MessageBox.Show(sourceFile + " will be import!", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)))
                {
                    this.Refresh();
                    ApplicationClass app = new ApplicationClass();
                    Workbook book = null;
                    Worksheet sheet = null;
                    Range range = null;
                    int[] Columns = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52 };

                    try
                    {
                        app.Visible = false;
                        app.ScreenUpdating = false;
                        app.DisplayAlerts = false;

                        book = app.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing
                                              , Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                             , Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                            , Type.Missing, Type.Missing, Type.Missing);
                        sheet = (Worksheet)book.Worksheets[1];
                        range = sheet.get_Range("A2", Type.Missing);
                        range = range.get_End(XlDirection.xlToRight);
                        range = range.get_End(XlDirection.xlDown);
                        string downAddress = range.get_Address(
                            false, false, XlReferenceStyle.xlA1,
                            Type.Missing, Type.Missing);
                        range = sheet.get_Range("A2", downAddress);
                        object[,] values = (object[,])range.Value2;
                        DALclient dac = new DALclient();
                        //dac.DeleteDailyOrderByDate(transDate);
                        for (int i = 2; i <= values.GetLength(0); i++)
                        {
                            try
                            {
                                dac.InsertDailySummary(transDate.Date,
                                values[i, 1].ToString(), //symbol
                                Convert.ToInt32(Convert.ToDouble(values[i, 2]) * 1000), //OpenPrice
                                Convert.ToInt32(Convert.ToDouble(values[i, 3]) * 1000), // HighPrice
                                Convert.ToInt32(Convert.ToDouble(values[i, 4]) * 1000), // LowPrice
                                Convert.ToInt32(Convert.ToDouble(values[i, 5]) * 1000), // ClosePrice
                                (float.Parse(values[i, 6].ToString()) * 1000), // AveragePrice 
                                Convert.ToInt32(values[i, 7]), //Total
                                Convert.ToInt32(values[i, 8]), //P1
                                Convert.ToInt64(values[i, 20]), //P1value
                                Convert.ToInt32(values[i, 10]), //P2
                                Convert.ToInt64(values[i, 21]), //P2value
                                Convert.ToInt32(values[i, 9]), //P3
                                Convert.ToInt64(values[i, 22]), //P3value
                                Convert.ToInt32(values[i, 27]), //BuyOrder
                                Convert.ToInt32(values[i, 28]), //SellOrder, 
                                Convert.ToInt32(values[i, 25]), //BuyCount
                                Convert.ToInt32(values[i, 26]), //SellCount
                                Convert.ToInt32(values[i, 29]), //Pt
                                Convert.ToInt32(values[i, 30]), //PtValue 
                                Convert.ToInt64(values[i, 36]), //Listing
                                Convert.ToInt64(values[i, 35]), //In market
                                float.Parse(values[i, 37].ToString()), //EPS
                                float.Parse(values[i, 38].ToString()), //Adjusted EPS
                                Convert.ToInt32(Convert.ToDouble(values[i, 39]) * 1000), //Benefit
                                float.Parse(values[i, 42].ToString()), //Foreign own
                                float.Parse(values[i, 43].ToString()),//State own
                                "HOSE");

                                if ((Convert.ToInt32(values[i, 11]) > 0) || (Convert.ToInt32(values[i, 12]) > 0) || (Convert.ToInt32(values[i, 31]) > 0) || (Convert.ToInt32(values[i, 32]) > 0))
                                    dac.InsertDailyForeignTrade(transDate.Date,
                                    values[i, 1].ToString(), //symbol
                                    Convert.ToInt32(values[i, 11]), //Buy
                                    Convert.ToInt64(values[i, 23]), //BuyValue
                                    Convert.ToInt32(values[i, 12]), //Sell
                                    Convert.ToInt64(values[i, 24]), //BuyValue
                                    Convert.ToInt32(values[i, 13]), //BuyP1
                                    Convert.ToInt32(values[i, 14]), //BuyP1
                                    Convert.ToInt32(values[i, 15]), //BuyP1
                                    Convert.ToInt32(values[i, 16]), //SellP1
                                    Convert.ToInt32(values[i, 17]), //SellP1
                                    Convert.ToInt32(values[i, 18]), //SellP1
                                    Convert.ToInt32(values[i, 31]), //pt buy
                                    Convert.ToInt64(values[i, 33]), //pt buy value
                                    Convert.ToInt32(values[i, 32]), //pt sell
                                    Convert.ToInt64(values[i, 34]), //pt sell value
                                    Convert.ToInt32(values[i, 40]), //room
                                    Convert.ToInt32(values[i, 41])); //remain

                                if (Convert.ToInt32(values[i, 46]) > 0)
                                    dac.InsertDailyFundTrade(transDate.Date,
                                    values[i, 1].ToString(), //symbol
                                    values[i, 44].ToString(), //Side
                                    Convert.ToInt64(values[i, 45]), //register
                                    Convert.ToInt32(values[i, 46]), //Inday
                                    float.Parse(values[i, 47].ToString()), //Inday Ratio
                                    Convert.ToInt32(values[i, 48]), //Accumulate
                                    float.Parse(values[i, 49].ToString()), //Accumulate Ratio
                                    Convert.ToInt32(values[i, 50]), //Remain
                                    float.Parse(values[i, 51].ToString()), //RemainRatio
                                    ConvertToDateTime(Convert.ToDouble(values[i, 52])) //register date
                                    );

                            }
                            catch (System.Exception e)
                            {

                            }
                        }


                        //Hastc
                        sheet = (Worksheet)book.Worksheets[2];
                        range = sheet.get_Range("A2", Type.Missing);
                        range = range.get_End(XlDirection.xlToRight);
                        range = range.get_End(XlDirection.xlDown);
                        downAddress = range.get_Address(
                            false, false, XlReferenceStyle.xlA1,
                            Type.Missing, Type.Missing);
                        range = sheet.get_Range("A2", downAddress);
                        values = (object[,])range.Value2;
                        for (int i = 2; i <= values.GetLength(0); i++)
                        {
                            try
                            {
                                dac.InsertDailySummary(transDate.Date,
                                values[i, 1].ToString(), //symbol
                                Convert.ToInt32(Convert.ToDouble(values[i, 2]) * 1000), //OpenPrice
                                Convert.ToInt32(Convert.ToDouble(values[i, 3]) * 1000), // HighPrice
                                Convert.ToInt32(Convert.ToDouble(values[i, 4]) * 1000), // LowPrice
                                Convert.ToInt32(Convert.ToDouble(values[i, 5]) * 1000), // ClosePrice
                                (float.Parse(values[i, 6].ToString()) * 1000), // AveragePrice 
                                Convert.ToInt32(values[i, 7]), //Total
                                Convert.ToInt32(values[i, 8]), //P1
                                Convert.ToInt64(values[i, 20]), //P1value
                                Convert.ToInt32(values[i, 10]), //P2
                                Convert.ToInt64(values[i, 21]), //P2value
                                Convert.ToInt32(values[i, 9]), //P3
                                Convert.ToInt64(values[i, 22]), //P3value
                                Convert.ToInt32(values[i, 27]), //BuyOrder
                                Convert.ToInt32(values[i, 28]), //SellOrder, 
                                Convert.ToInt32(values[i, 25]), //BuyCount
                                Convert.ToInt32(values[i, 26]), //SellCount
                                Convert.ToInt32(values[i, 29]), //Pt
                                Convert.ToInt32(values[i, 30]), //PtValue 
                                Convert.ToInt64(values[i, 36]), //Listing
                                Convert.ToInt64(values[i, 35]), //In market
                                float.Parse(values[i, 37].ToString()), //EPS
                                float.Parse(values[i, 38].ToString()), //Adjusted EPS
                                Convert.ToInt32(Convert.ToDouble(values[i, 39]) * 1000), //Benefit
                                float.Parse(values[i, 42].ToString()), //Foreign own
                                float.Parse(values[i, 43].ToString()),//State own
                                "HASTC");

                                if ((Convert.ToInt32(values[i, 11]) > 0) || (Convert.ToInt32(values[i, 12]) > 0) || (Convert.ToInt32(values[i, 31]) > 0) || (Convert.ToInt32(values[i, 32]) > 0))
                                    dac.InsertDailyForeignTrade(transDate.Date,
                                    values[i, 1].ToString(), //symbol
                                    Convert.ToInt32(values[i, 11]), //Buy
                                    Convert.ToInt64(values[i, 23]), //BuyValue
                                    Convert.ToInt32(values[i, 12]), //Sell
                                    Convert.ToInt64(values[i, 24]), //BuyValue
                                    Convert.ToInt32(values[i, 13]), //BuyP1
                                    Convert.ToInt32(values[i, 14]), //BuyP1
                                    Convert.ToInt32(values[i, 15]), //BuyP1
                                    Convert.ToInt32(values[i, 16]), //SellP1
                                    Convert.ToInt32(values[i, 17]), //SellP1
                                    Convert.ToInt32(values[i, 18]), //SellP1
                                    Convert.ToInt32(values[i, 31]), //pt buy
                                    Convert.ToInt64(values[i, 33]), //pt buy value
                                    Convert.ToInt32(values[i, 32]), //pt sell
                                    Convert.ToInt64(values[i, 34]), //pt sell value
                                    Convert.ToInt32(values[i, 40]), //room
                                    Convert.ToInt32(values[i, 41])); //remain

                                if (Convert.ToInt32(values[i, 46]) > 0)
                                    dac.InsertDailyFundTrade(transDate.Date,
                                    values[i, 1].ToString(), //symbol
                                    values[i, 44].ToString(), //Side
                                    Convert.ToInt64(values[i, 45]), //register
                                    Convert.ToInt32(values[i, 46]), //Inday
                                    float.Parse(values[i, 47].ToString()), //Inday Ratio
                                    Convert.ToInt32(values[i, 48]), //Accumulate
                                    float.Parse(values[i, 49].ToString()), //Accumulate Ratio
                                    Convert.ToInt32(values[i, 50]), //Remain
                                    float.Parse(values[i, 51].ToString()), //RemainRatio
                                    ConvertToDateTime(Convert.ToDouble(values[i, 52])) //register date
                                    );

                            }
                            catch (System.Exception e)
                            {

                            }
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        range = null;
                        sheet = null;
                        if (book != null)
                            book.Close(false, Type.Missing, Type.Missing);
                        book = null;
                        if (app != null)
                            app.Quit();
                        app = null;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message, "Import result", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public static DateTime ConvertToDateTime(double excelDate)
        {
            if (excelDate < 1)
            {
                throw new ArgumentException("Excel dates cannot be smaller than 0.");
            }
            DateTime dateOfReference = new DateTime(1900, 1, 1);
            if (excelDate > 60d)
            {
                excelDate = excelDate - 2;
            }
            else
            {
                excelDate = excelDate - 1;
            }
            return dateOfReference.AddDays(excelDate);
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            DataConvertion frmCnt = new DataConvertion(this);
            frmCnt.Show();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            #region Hide unchecked Properties
            #endregion
        }

        private void clbPrice_ItemCheck(object sender, ItemCheckEventArgs e)
        {
            switch (e.Index.ToString())
            {
                case "0":
                    grvDetail.Columns["OpenPrice"].Visible = (e.CurrentValue == CheckState.Checked);
                    break;
                default:
                    break;
            }

        }

    }
}
